www.gusucode.com > 通过ODBC API实现对数据库的访问JavaScript源码程序 > 通过ODBC API实现对数据库的访问/ODBCApiDataManager/ODBCApiDataManager/DbOperator/DbOperator.cpp

    #include "StdAfx.h"
#include ".\dboperator.h"
CDbOperator::CDbOperator(void)
{
	m_strDSN = "DbMarket";
	m_strUSER = "hskj";
	m_strPWD = "newtech";

	henv = SQL_NULL_HANDLE;
	hdbc = SQL_NULL_HANDLE;

	m_bLink = FALSE;
	OpenDatabase();
}

CDbOperator::~CDbOperator(void)
{
	if(m_bLink)
	{
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		m_bLink = FALSE;
	}
}

CDbOperator::CDbOperator(CString strDSN, CString strUSER, CString strPWD)
{	
	henv = SQL_NULL_HANDLE;
	hdbc = SQL_NULL_HANDLE;

	m_strDSN = strDSN;
	m_strUSER = strUSER;
	m_strPWD = strPWD;

	m_bLink = FALSE;
	OpenDatabase();
}

BOOL CDbOperator::OpenDatabase()
{
	SQLINTEGER cbLenth = 0 ;	
	SQLRETURN retcode;

	retcode = SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"SQL Server","DSN=DbMarket\0Description=odbctest\0Server=(local)\0Database=DbMarket\0\0");
	if(!retcode)
	{
		AfxMessageBox("系统数据源配置失败!");
		return FALSE;
	}

	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) ;
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{
		retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
		{
			retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
			if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
			{
				retcode = SQLConnect(hdbc, (SQLCHAR*)(LPCTSTR)m_strDSN, SQL_NTS, (SQLCHAR*)(LPCTSTR)m_strUSER, SQL_NTS, 
					(SQLCHAR*)(LPCTSTR)m_strPWD, SQL_NTS);
				if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
				{
					AfxMessageBox("数据库连接失败!") ;
					SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 
					SQLFreeHandle(SQL_HANDLE_ENV, henv);
					return FALSE;
				}
			} 
			else
			{
				AfxMessageBox("连接句柄分配出错") ;
				SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 
				SQLFreeHandle(SQL_HANDLE_ENV, henv);
				return FALSE;
			}
		}
		else
		{
			AfxMessageBox("属性设置出错！") ;
			SQLFreeHandle(SQL_HANDLE_ENV, henv);
			return FALSE;
		}
	}
	else
	{
		AfxMessageBox("环境变量分配出错！") ;
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		return FALSE;
	}
	m_bLink = TRUE;
	return TRUE;
}

BOOL CDbOperator::IsTableExisted(CString strTableName)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	CString strSQL;
	strSQL.Format("SELECT * FROM sysobjects WHERE name='%s'", strTableName);
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;
		}
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO))
		{		
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return  TRUE;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}			
	}
	else
	{	
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
}

BOOL CDbOperator::CreateTable(CString strSQL)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return TRUE;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}			
	}
	else
	{	
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}	
}

BOOL CDbOperator::AlterTable(CString strSQL)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return TRUE;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}			
	}
	else
	{	
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
}

BOOL CDbOperator::DropTable(CString strSQL)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return TRUE;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}			
	}
	else
	{	
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
}

BOOL CDbOperator::InitAllTable()
{
	CStdioFile file;
	CFileException fileException;
	try
	{
		file.Open("Database.txt", CFile::modeRead | CFile::typeText, &fileException );
	}
	catch(CFileException* pEx)
	{
		char szError[1024];
		ZeroMemory(szError, 1024);
		pEx->GetErrorMessage(szError, 1024);
		AfxMessageBox(szError);
		return FALSE;
	}
	CString strSQL;
	CString strTableName;
	BOOL bIsTableExisted;
	BOOL bIsCreate;
	while(file.ReadString(strSQL))
	{
		if(GetTableNameFromSql(strSQL, strTableName))
		{
			bIsTableExisted = IsTableExisted(strTableName);
			if(!bIsTableExisted)
			{
				bIsCreate = CreateTable(strSQL);
				if(!bIsCreate)
				{
					AfxMessageBox("创建表失败！");
					return FALSE;
				}
			}
			else
			{
				//更新数据库，但暂不做更新
			}
		}
	}
	AfxMessageBox("创建表成功！");
	file.Close();
	//插入初始化表内容
	return TRUE;
}


BOOL CDbOperator::InsertContent(CString strSQL)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return TRUE;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}			
	}
	else
	{	
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
}
BOOL CDbOperator::InitTableContent()
{
	CStdioFile file;
	CFileException fileException;
	try
	{
		file.Open("TableContent.txt", CFile::modeRead | CFile::typeText, &fileException );
	}
	catch(CFileException* pEx)
	{
		char szError[1024];
		ZeroMemory(szError, 1024);
		pEx->GetErrorMessage(szError, 1024);
		AfxMessageBox(szError);
		return FALSE;
	}
	CString strSQL;
	while(file.ReadString(strSQL))
	{
		BOOL bIsInsert = InsertContent(strSQL);
		if(!bIsInsert)
		{
			AfxMessageBox("插入失败");
			return FALSE;
		}
		else
		{
			//更新数据库，但暂不做更新
		}
	}
	file.Close();
	return TRUE;
}

BOOL CDbOperator::GetTableNameFromSql(CString strSQL, CString& strTableName)
{
	//创建表的格式都是固定的模式：CREATE TABLE [TableName] ...
	int nTableBegin = strSQL.Find("[");
	if(nTableBegin == -1)
	{
		AfxMessageBox("没有找到相应的表头创建格式");
		return FALSE;
	}
	int nTableEnd = strSQL.Find(']');
	strTableName = strSQL.Mid(nTableBegin+1, nTableEnd-nTableBegin-1);
	return TRUE;
}

BOOL CDbOperator::GetUserIdFromObj_User(int arrUserId[], int& nUserCount)
{
	int nUserId = 0;
	nUserCount = 0;
	CString strSQL;

	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;
	strSQL = "SELECT User_Iden FROM Obj_User";
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		SQLBindCol(hstmt, 1, SQL_C_ULONG, (SQLPOINTER)&nUserId, sizeof(nUserId), &cbLenth);	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR)
		{
			AfxMessageBox("数据操作失败！") ;
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;
		}
		while((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO))
		{
			arrUserId[nUserCount] = nUserId;
			nUserCount++; 		
		}
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
		return  TRUE;		
	}
	else
	{	
		AfxMessageBox("数据操作失败！") ;
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
	return TRUE;
}

BOOL CDbOperator::GetNinaByUserId(int nUserId, CString& strNickname)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;
	char cNickname[100];
	ZeroMemory(cNickname, 100);

	CString strSQL;
	strSQL.Format("SELECT User_Nina FROM Obj_User WHERE User_Iden=%d", nUserId);
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)cNickname, 100, &cbLenth);	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR)
		{
			AfxMessageBox("数据操作失败！") ;
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;
		}
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO))
		{		
			strNickname = cNickname;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
		return  TRUE;		
	}
	else
	{	
		AfxMessageBox("获取电业局数据操作失败！") ;
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
}

BOOL CDbOperator::GetUserpwdByUsernina(CString strUsernina, CString& strUserpwd)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;
	char cUserpwd[100];
	ZeroMemory(cUserpwd, 100);

	CString strSQL;
	strSQL.Format("SELECT User_Pawo FROM Obj_User WHERE User_Nina='%s'", strUsernina);
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)cUserpwd, 100, &cbLenth);	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR)
		{
			AfxMessageBox("数据操作失败！") ;
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;
		}
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO))
		{		
			strUserpwd = cUserpwd;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
		return  TRUE;		
	}
	else
	{	
		AfxMessageBox("获取电业局数据操作失败！") ;
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
	return TRUE;
}

BOOL CDbOperator::GetAllUserTable(CString strTableName[], int& nTableCount)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;
	char cName[100];
	ZeroMemory(cName, 100);
	nTableCount = 0;

	CString strSQL;
	strSQL.Format("SELECT name FROM sysobjects WHERE xtype='%s'", "U");
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)cName, 100, &cbLenth);	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR)
		{
			AfxMessageBox("数据操作失败！") ;
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;
		}
		while((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO))
		{		
			strTableName[nTableCount] = cName;
			nTableCount++;
		}
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
		return  TRUE;		
	}
	else
	{	
		AfxMessageBox("获取电业局数据操作失败！") ;
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
	return TRUE;
}